﻿using System;
using System.Web;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using System.Data.Common;
using System.Data;
using System.Data.OleDb;
using System.Collections.Specialized;
//using System.Data.OracleClient;
using Oracle.DataAccess;
using Oracle.DataAccess.Client;
using System.Configuration;

//using Microsoft.SqlServer.Management.Smo;
//using Microsoft.SqlServer.Server;
//using Microsoft.SqlServer.Management.Common;
//using Microsoft.SqlServer;


namespace HMS.Common
{
    public class DBParameter
    {
        public string name { get; set; }
        public DbType dbType { get; set; }
        public ParameterDirection direction { get; set; }
        public object value { get; set; }
        public int size { get; set; }

        public DBParameter()
        {
        }

        public DBParameter(string name, DbType dbType, ParameterDirection direction)
        {
            this.name = name;
            this.dbType = dbType;
            this.direction = direction;
        }

        public DBParameter(string name, DbType dbType, ParameterDirection direction, int size)
        {
            this.name = name;
            this.dbType = dbType;
            this.direction = direction;
            this.size = size;
        }

        public DBParameter(string name, DbType dbType, ParameterDirection direction, object value)
        {
            this.name = name;
            this.dbType = dbType;
            this.direction = direction;
            this.value = value;
            this.size = size;
        }

        public DBParameter(string name, DbType dbType, ParameterDirection direction, int size, object value)
        {
            this.name = name;
            this.dbType = dbType;
            this.direction = direction;
            this.value = value;
            this.size = size;
        }
    }

    public class Connection : IDisposable
    {

        SqlConnection sqlConn = null;
        private LoadOption OverwriteChanges;

        public Connection()
        { }

        public SqlConnection OpenConnection()
        {
            sqlConn = new SqlConnection(ConfigurationManager.ConnectionStrings[Constants.CONNECTION_STRING].ToString());
            sqlConn.Open();
            return sqlConn;
        }

        public void CloseConnection()
        {
            if (sqlConn == null) return;
            if (sqlConn.State == ConnectionState.Open) sqlConn.Close();
            sqlConn.Dispose();
        }

        public DataSet GetDataSet(string strQry)
        {
            try
            {
                SqlCommand sqlComm = new SqlCommand();
                sqlComm.CommandText = strQry;
                sqlConn = this.OpenConnection();
                sqlComm.Connection = sqlConn;
                SqlDataAdapter sqlDa = new SqlDataAdapter(sqlComm);
                DataSet ds = new DataSet();
                sqlDa.Fill(ds);
                return ds;

            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                this.CloseConnection();
            }
        }


        // test DbProviderFactory  --strQuery -Select query
        public DataSet GetDataSetFromFactory(string strQry)
        {
            try
            {

                string connectionString = ConfigurationManager.ConnectionStrings[Constants.CONNECTION_STRING].ToString();
                DbProviderFactory factory = GetProvider(ConfigurationManager.AppSettings["DBType"].ToString());

                using (DbConnection connection = factory.CreateConnection())
                {

                    connection.ConnectionString = connectionString;
                    using (DbCommand command = connection.CreateCommand())
                    {
                        command.CommandText = strQry;
                        command.CommandType = CommandType.Text;
                        connection.Open();
                        DbDataAdapter dbDa = factory.CreateDataAdapter();
                        dbDa.SelectCommand = command;
                        DataSet ds = new DataSet();
                        dbDa.Fill(ds);
                        return ds;
                    }
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        // test DbProviderFactory  --strQuery -Select query
        public DataSet GetDataSetFromExcel(string strQry, string strfilePath)
        {
            try
            {

                string connectionString = ConfigurationManager.ConnectionStrings[Constants.CONNECTION_STRING_EXCEL].ToString();
                //HttpContext.Current.Server.MapPath("~/App_Data/Example.xml");
                //string fname = HttpContext.Current.Server.MapPath("..\\Excel") + "\\" + strfileName;

                connectionString = connectionString.Replace("$$", strfilePath);

                // string connectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=c:\\Contact.xlsx;Extended Properties=\"Excel 12.0 Xml;HDR=YES\";";
                DbProviderFactory factory = GetProvider("OLEDB");

                using (DbConnection connection = factory.CreateConnection())
                {

                    connection.ConnectionString = connectionString;
                    using (DbCommand command = connection.CreateCommand())
                    {
                        command.CommandText = strQry;
                        command.CommandType = CommandType.Text;
                        connection.Open();
                        DbDataAdapter dbDa = factory.CreateDataAdapter();
                        dbDa.SelectCommand = command;
                        DataSet ds = new DataSet();
                        dbDa.Fill(ds);
                        return ds;
                    }
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }


        // check if a sheetname exist in excel file
        public DataTable GetSheetNameFromExcel(string strfilePath)
        {
            try
            {

                string connectionString = ConfigurationManager.ConnectionStrings[Constants.CONNECTION_STRING_EXCEL].ToString();
                //HttpContext.Current.Server.MapPath("~/App_Data/Example.xml");
                //string fname = HttpContext.Current.Server.MapPath("..\\Excel") + "\\" + strfileName;

                connectionString = connectionString.Replace("$$", strfilePath);
                DataTable dtExcel;
                // string connectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=c:\\Contact.xlsx;Extended Properties=\"Excel 12.0 Xml;HDR=YES\";";
                DbProviderFactory factory = GetProvider("OLEDB");

                using (DbConnection connection = factory.CreateConnection())
                {

                    connection.ConnectionString = connectionString;
                    using (DbCommand command = connection.CreateCommand())
                    {
                        connection.Open();
                        dtExcel = connection.GetSchema("Tables");
                    }
                }

                return dtExcel;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }


        // test DbProviderFactory  --strQuery -Select query
        public Boolean BatchUpdatedbWithAdapter(string strQry, DataTable dtexcel, Int32 batchSize)
        {
            try
            {

                string connectionString = ConfigurationManager.ConnectionStrings[Constants.CONNECTION_STRING].ToString();
                DbProviderFactory factory = GetProvider(ConfigurationManager.AppSettings["DBType"].ToString());

                using (DbConnection connection = factory.CreateConnection())
                {

                    connection.ConnectionString = connectionString;
                    using (DbCommand command = connection.CreateCommand())
                    {

                        command.CommandText = strQry;
                        command.CommandType = CommandType.Text;

                        connection.Open();

                        using (DbTransaction transaction = connection.BeginTransaction())
                        {

                            command.Transaction = transaction;
                            DbDataAdapter dbDa = factory.CreateDataAdapter();
                            dbDa.SelectCommand = command;

                            DbCommandBuilder dbbuilder = factory.CreateCommandBuilder();
                            //  DbCommandBuilder dbbuilder1 = factory.CreateCommandBuilder
                            dbbuilder.DataAdapter = dbDa;

                            // Get the insert, update and delete commands.
                            //dbDa.InsertCommand = dbbuilder.GetInsertCommand();
                            dbDa.UpdateCommand = dbbuilder.GetUpdateCommand();
                            //dbDa.DeleteCommand = dbbuilder.GetDeleteCommand();                    
                            DataSet ds = new DataSet();
                            dbDa.Fill(ds);

                            //foreach (DataRow row in dtexcel.Rows)
                            //{                           
                            //    ds.Tables[0].Rows.Add(row.ItemArray);
                            //}

                            //insert datarows from excel datatable
                            //ds=UpdateDataTableFromExcel(ds, dtexcel);

                            DataTableReader reader = new DataTableReader(dtexcel);
                            ds.Tables[0].Load(reader, LoadOption.Upsert);
                            //ds.Tables[0].LoadDataRow(reader, LoadOption.Upsert);

                            //ds.Tables[0].Load(reader, OverwriteChanges);
                            //ds.Tables[0].Load(reader, PreserveChanges);
                            //ds.Tables[0].Load(reader, Upsert);

                            //ds.Tables[0].Rows[0].SetAdded();                      
                            // Set the batch size.
                            dbDa.UpdateBatchSize = batchSize;
                            dbDa.Update(ds.Tables[0]);
                            transaction.Commit();
                            return true;
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public DataSet UpdateDataTableFromExcel(DataSet ds, DataTable dtexcel)
        {

            try
            {
                foreach (DataRow row in dtexcel.Rows)
                {
                    ds.Tables[0].Rows.Add(row.ItemArray);
                }
                return ds;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        // test DbProviderFactory  --strQuery -Select query
        public Int32 SavedataFromExcel__(string strQry)
        {
            try
            {

                string connectionString = ConfigurationManager.ConnectionStrings[Constants.CONNECTION_STRING_EXCEL].ToString();
                DbProviderFactory factory = GetProvider("OLEDB");

                using (DbConnection connection = factory.CreateConnection())
                {

                    connection.ConnectionString = connectionString;
                    using (DbCommand command = connection.CreateCommand())
                    {

                        command.CommandText = strQry;
                        command.CommandType = CommandType.Text;
                        connection.Open();
                        command.Connection = connection;
                        Int32 intRet = command.ExecuteNonQuery();
                        return intRet;
                    }
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public void SetCommandParameters(SqlCommand oCommand, string Name, SqlDbType DataType, int size, string Direction, int Scale = 0, int Precision = 0, object oValue = null)
        {

            SqlParameter oParam = new SqlParameter();

            if (size == 0)
            {
                oParam = oCommand.Parameters.Add(Name, DataType);
            }
            else
            {
                oParam = oCommand.Parameters.Add(Name, DataType, size);
            }

            var _with1 = oParam;
            if (Direction == "I")
                _with1.Direction = ParameterDirection.Input;
            if (Direction == "O")
                _with1.Direction = ParameterDirection.Output;
            if (Direction == "IO")
                _with1.Direction = ParameterDirection.InputOutput;
            if (Direction == "R")
                _with1.Direction = ParameterDirection.ReturnValue;
            //if (!(Scale == 0))
            //    _with1.Scale = Scale;
            //if (!(Precision == 0))
            //    _with1.Precision = Precision;
            //if (Direction == "I" | Direction == "IO")
            //    _with1.Value = oValue;
        }

        //test DbProviderFactory with procedure name without parameter collection
        public DataSet GetDataSetFromFactoryProc(string strProcName)
        {
            try
            {
                string connectionString = ConfigurationManager.ConnectionStrings[Constants.CONNECTION_STRING].ToString();
                DbProviderFactory factory = GetProvider(ConfigurationManager.AppSettings["DBType"].ToString());

                using (DbConnection connection = factory.CreateConnection())
                {

                    connection.ConnectionString = connectionString;
                    using (DbCommand command = connection.CreateCommand())
                    {

                        command.CommandText = strProcName;
                        command.CommandType = CommandType.StoredProcedure;
                        connection.Open();
                        DbDataAdapter dbDa = factory.CreateDataAdapter();
                        dbDa.SelectCommand = command;
                        DataSet ds = new DataSet();
                        dbDa.Fill(ds);
                        return ds;
                    }
                }
            }
            catch (Exception ex)
            {
                throw;
            }
        }

        //test DbProviderFactory with procedure name without parameter collection
        public DataSet GetDataSetFromFactoryProc(string strProcName, ref List<DBParameter> parameters)
        {
            try
            {
                string connectionString = ConfigurationManager.ConnectionStrings[Constants.CONNECTION_STRING].ToString();
                DbProviderFactory factory = GetProvider(ConfigurationManager.AppSettings["DBType"].ToString());

                using (DbConnection connection = factory.CreateConnection())
                {

                    connection.ConnectionString = connectionString;
                    using (DbCommand command = connection.CreateCommand())
                    {

                        command.CommandText = strProcName;
                        command.CommandType = CommandType.StoredProcedure;
                        command.Parameters.AddRange(CreateParameter(command, parameters));
                        connection.Open();
                        DbDataAdapter dbDa = factory.CreateDataAdapter();
                        dbDa.SelectCommand = command;
                        DataSet ds = new DataSet();
                        dbDa.Fill(ds);
                        for (int i = 0; i < parameters.Count; i++)
                        {
                            if (command.Parameters[i].Direction == ParameterDirection.InputOutput || command.Parameters[i].Direction == ParameterDirection.Output)
                            {
                                parameters[i].value = command.Parameters[i].Value;
                            }
                        }
                        return ds;
                    }
                }
            }
            catch (Exception ex)
            {
                throw;
            }
        }

        private DbParameter[] CreateParameter(DbCommand command, List<DBParameter> parameters)
        {
            DbParameter[] dbParameter = null;
            try
            {
                List<DbParameter> listParams = new List<DbParameter>();
                foreach (DBParameter param in parameters)
                {
                    DbParameter dbParam = command.CreateParameter();
                    dbParam.DbType = param.dbType;
                    dbParam.Direction = param.direction;
                    dbParam.ParameterName = param.name;
                    dbParam.Size = param.size;
                    if (param.value != null)
                    {
                        dbParam.Value = param.value;
                    }
                    listParams.Add(dbParam);
                }
                dbParameter = listParams.ToArray();
            }
            catch (Exception ex)
            {
                throw;
            }
            return dbParameter;
        }

        // test DbProviderFactory  --strQuery -Select query + with open connection
        //public DataSet GetDataSetFromFactoryWithConn(string strQry, DbConnection connection)
        //{
        //    try
        //    {

        //        string connectionString = ConfigurationManager.ConnectionStrings[Constants.CONNECTION_STRING].ToString();
        //        DbProviderFactory factory = GetProvider(ConfigurationManager.AppSettings["DBType"].ToString());

        //       // using (DbConnection connection = factory.CreateConnection())
        //       // {

        //           // connection.ConnectionString = connectionString;
        //            using (DbCommand command = connection.CreateCommand())
        //            {

        //                command.CommandText = strQry;
        //                command.CommandType = CommandType.Text;
        //              //  connection.Open();
        //                DbDataAdapter dbDa = factory.CreateDataAdapter();
        //                dbDa.SelectCommand = command;
        //                DataSet ds = new DataSet();
        //                dbDa.Fill(ds);
        //                return ds;
        //            }
        //       // }
        //    }
        //    catch (Exception ex)
        //    {
        //        throw;
        //    }
        //}

        public DbProviderFactory GetProvider(string strDbType)
        {

            //      string ConStr = "User Id=scott;Password=tiger;" +
            //"Data Source=orc1;";
            //      OracleConnection con = new OracleConnection(ConStr);
            //      con.Open();
            //      OracleCommand cmd = con.CreateCommand();

            //      cmd.CommandText = "select * from t";
            //      cmd.CommandType = CommandType.Text;
            //      //cmd.ExecuteNonQuery();

            //      OracleDataAdapter dbDa = new OracleDataAdapter();
            //      dbDa.SelectCommand = cmd;
            //      DataSet ds = new DataSet();
            //      dbDa.Fill(ds);

            DbProviderFactory factory = null;
            //factory = SqlClientFactory.Instance;
            if (strDbType.ToString().ToUpper() == "SQL")
            {
                factory = SqlClientFactory.Instance;
            }
            else if (strDbType == "OLEDB")
            {
                factory = OleDbFactory.Instance;
            }
            else if (strDbType == "ORACLE")
            {
                //factory = OracleClientFactory.Instance;
                factory = DbProviderFactories.GetFactory("Oracle.DataAccess.Client");
            }
            else if (strDbType == "SQLOLEDB")
            {
                //factory = OracleClientFactory.Instance;
                factory = DbProviderFactories.GetFactory("SQLOLEDB");
            }
            return factory;

        }

        //save data
        public Int32 SaveDataByFactory(string strQry)
        {
            try
            {

                string connectionString = ConfigurationManager.ConnectionStrings[Constants.CONNECTION_STRING].ToString();
                DbProviderFactory factory = GetProvider(ConfigurationManager.AppSettings["DBType"].ToString());

                using (DbConnection connection = factory.CreateConnection())
                {

                    connection.ConnectionString = connectionString;
                    using (DbCommand command = connection.CreateCommand())
                    {

                        command.CommandText = strQry;
                        command.CommandType = CommandType.Text;
                        connection.Open();
                        command.Connection = connection;
                        Int32 intRet = command.ExecuteNonQuery();
                        return intRet;
                    }
                }
            }
            catch (Exception ex)
            {
                throw;
            }
        }

        //save data
        public Int32 SaveDataByFactoryByProc(string strProcName, ref List<DBParameter> parameters)
        {
            try
            {

                string connectionString = ConfigurationManager.ConnectionStrings[Constants.CONNECTION_STRING].ToString();
                DbProviderFactory factory = GetProvider(ConfigurationManager.AppSettings["DBType"].ToString());

                using (DbConnection connection = factory.CreateConnection())
                {

                    connection.ConnectionString = connectionString;
                    using (DbCommand command = connection.CreateCommand())
                    {
                        command.CommandText = strProcName;
                        command.CommandType = CommandType.StoredProcedure;
                        command.Parameters.AddRange(CreateParameter(command, parameters));
                        connection.Open();
                        command.Connection = connection;
                        Int32 intRet = command.ExecuteNonQuery();
                        for (int i = 0; i < parameters.Count; i++)
                        {
                            if (command.Parameters[i].Direction == ParameterDirection.InputOutput || command.Parameters[i].Direction == ParameterDirection.Output || command.Parameters[i].Direction == ParameterDirection.ReturnValue)
                            {
                                parameters[i].value = command.Parameters[i].Value;
                            }
                        }

                        return intRet;
                    }
                }
            }
            catch (Exception ex)
            {
                throw;
            }
        }



        //save data by transaction + dbproviderfactory
        public Int32 SaveDataByFactory(string strQry, DbConnection connection, DbTransaction trans)
        {
            try
            {
                using (DbCommand command = connection.CreateCommand())
                {

                    command.CommandText = strQry;
                    command.CommandType = CommandType.Text;
                    command.Transaction = trans;
                    command.Connection = connection;
                    Int32 intRet = command.ExecuteNonQuery();
                    return intRet;
                }

            }
            catch (Exception ex)
            {
                trans.Rollback();
                throw ex;
            }
        }

        //get open connection with transaction
        public DbConnection GetConnection()
        {
            try
            {

                string connectionString = ConfigurationManager.ConnectionStrings[Constants.CONNECTION_STRING].ToString();
                DbProviderFactory factory = GetProvider(ConfigurationManager.AppSettings["DBType"].ToString());
                DbConnection connection = factory.CreateConnection();
                connection.ConnectionString = connectionString;
                connection.Open();
                return connection;
            }
            catch (Exception ex)
            {

                throw ex;
            }
        }

        //close dbconnection
        public void CloseDbConnection(DbConnection conn, DbTransaction trans)
        {
            if (conn == null) return;
            if (conn.State == ConnectionState.Open) conn.Close();
            trans.Dispose();
            conn.Dispose();

        }

        //execute scalar by ExecuteScalarByFactory
        public string ExecuteScalarByFactory(string strQry)
        {
            try
            {

                string connectionString = ConfigurationManager.ConnectionStrings[Constants.CONNECTION_STRING].ToString();
                DbProviderFactory factory = GetProvider(ConfigurationManager.AppSettings["DBType"].ToString());

                using (DbConnection connection = factory.CreateConnection())
                {

                    connection.ConnectionString = connectionString;
                    using (DbCommand command = connection.CreateCommand())
                    {

                        command.CommandText = strQry;
                        command.CommandType = CommandType.Text;
                        connection.Open();
                        command.Connection = connection;
                        string intCnt = Convert.ToString(command.ExecuteScalar());
                        return intCnt;
                    }
                }
            }
            catch (Exception ex)
            {
                throw;
            }
        }


        //execute scalar by ExecuteScalarByFactory
        public string ExecuteScalarByFactory(string strQry, ref List<DBParameter> parameters)
        {
            try
            {

                string connectionString = ConfigurationManager.ConnectionStrings[Constants.CONNECTION_STRING].ToString();
                DbProviderFactory factory = GetProvider(ConfigurationManager.AppSettings["DBType"].ToString());

                using (DbConnection connection = factory.CreateConnection())
                {

                    connection.ConnectionString = connectionString;
                    using (DbCommand command = connection.CreateCommand())
                    {

                        command.CommandText = strQry;
                        command.CommandType = CommandType.StoredProcedure;
                        command.Parameters.AddRange(CreateParameter(command, parameters));
                        connection.Open();
                        command.Connection = connection;
                        string intCnt = Convert.ToString(command.ExecuteScalar());
                        return intCnt;
                    }
                }
            }
            catch (Exception ex)
            {
                throw;
            }
        }


        /// <summary>
        /// ////end of test Dbprovider factory
        /// </summary>
        /// <param name="strQry"></param>
        /// <returns></returns>
        public Int32 SaveData(string strQry)
        {
            try
            {
                SqlCommand sqlComm = new SqlCommand();
                sqlComm.CommandText = strQry;
                sqlConn = this.OpenConnection();
                sqlComm.Connection = sqlConn;
                Int32 intCnt = sqlComm.ExecuteNonQuery();
                return intCnt;
            }
            catch (Exception ex)
            {
                throw;
            }
            finally
            {
                this.CloseConnection();
            }
        }

        public void Dispose()
        {
            CloseConnection();
            GC.SuppressFinalize(this);
        }

        public int ExecuteCommand(string strQry)
        {
            try
            {
                SqlCommand sqlComm = new SqlCommand();
                sqlComm.CommandText = strQry;
                sqlConn = this.OpenConnection();
                sqlComm.Connection = sqlConn;
                int intCnt = sqlComm.ExecuteNonQuery();
                return intCnt;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                this.CloseConnection();
            }
        }

        public int ExecuteCommand(string strQry, bool returnId)
        {
            try
            {
                SqlCommand sqlComm = new SqlCommand();
                sqlComm.CommandText = strQry;
                sqlConn = this.OpenConnection();
                sqlComm.Connection = sqlConn;
                int intCnt = sqlComm.ExecuteNonQuery();
                return intCnt;
            }
            catch (Exception ex)
            {
                throw;
            }
            finally
            {
                this.CloseConnection();
            }
        }

        //public void SetdbCommandParameters(DbCommand oCommand, string Name, DbType DataType, int size, string Direction, int Scale = 0, int Precision = 0, object oValue = null)
        //{

        //    DbParameter oParam = new DbParameter();

        //    if (size == 0)
        //    {
        //        //oParam = oCommand.Parameters.Add(Name, DataType);
        //        oParam = oCommand.Parameters.Add(Name,DataType);
        //    }
        //    else
        //    {
        //        oParam = oCommand.Parameters.Add(Name, DataType, size);
        //    }

        //    var _with1 = oParam;
        //    if (Direction == "I")
        //        _with1.Direction = ParameterDirection.Input;
        //    if (Direction == "O")
        //        _with1.Direction = ParameterDirection.Output;
        //    if (Direction == "IO")
        //        _with1.Direction = ParameterDirection.InputOutput;
        //    if (Direction == "R")
        //        _with1.Direction = ParameterDirection.ReturnValue;
        //    //if (!(Scale == 0))
        //    //    _with1.Scale = Scale;
        //    //if (!(Precision == 0))
        //    //    _with1.Precision = Precision;
        //    //if (Direction == "I" | Direction == "IO")
        //    //    _with1.Value = oValue;
        //}


        ////save by procedure
        //public Int32 SaveDataByFactoryProc(string strProc,NameValueCollection  allkey)
        //{
        //    try
        //    {

        //        string connectionString = ConfigurationManager.ConnectionStrings[Constants.CONNECTION_STRING].ToString();
        //        DbProviderFactory factory = GetProvider(ConfigurationManager.AppSettings["DBType"].ToString());



        //        using (DbConnection connection = factory.CreateConnection())
        //        {

        //            connection.ConnectionString = connectionString;
        //            using (DbCommand command = connection.CreateCommand())
        //            {

        //                command.CommandText = strProc;
        //                command.CommandType = CommandType.StoredProcedure;
        //                connection.Open();
        //                command.Connection = connection;
        //                Int32 intRet = command.ExecuteNonQuery();
        //                return intRet;
        //            }
        //        }
        //    }
        //    catch (Exception ex)
        //    {
        //        throw;
        //    }

        //}


        //Set destination connection string

        //public int CreateTable(DataTable SourceTable)
        //{
        //    try
        //    {

        //        //SqlCommand sqlComm = new SqlCommand();
        //        //sqlComm.CommandText = strQry;
        //        //sqlConn = this.OpenConnection();
        //        //sqlComm.Connection = sqlConn;
        //        //int intCnt = sqlComm.ExecuteNonQuery();

        //        string connectionString = ConfigurationManager.ConnectionStrings[Constants.CONNECTION_STRING].ToString();
        //        SqlConnection Connection = new SqlConnection(connectionString);

        //        //SMO Server object setup with SQLConnection.
        //        Server server = new Server(new ServerConnection(Connection));

        //        //Create a new SMO Database giving server object and database name
        //        //Database db = new Database(server, "TestSMODatabase");
        //        //db.Create();

        //        //Set Database to the newly created database
        //        Database db = server.Databases["Tree"];

        //        //Create a new SMO table
        //        Table TestTable = new Table(db, "TestTable");
        //        //SMO Column object referring to destination table.
        //        Column tempC = new Column();

        //        //Add the column names and types from the datatable into the new table
        //        //Using the columns name and type property
        //        foreach (DataColumn dc in SourceTable.Columns)
        //        {
        //            //Create columns from datatable column schema
        //            tempC = new Column(TestTable, dc.ColumnName);
        //            tempC.DataType = GetDataType(dc.DataType.ToString());

        //            TestTable.Columns.Add(tempC);
        //        }
        //        //Create the Destination Table
        //        TestTable.Create();


        //        //Open a connection with destination database;
        //        using (SqlConnection connection =
        //               new SqlConnection(connectionString))
        //        {
        //            connection.Open();

        //            //Open bulkcopy connection.
        //            using (SqlBulkCopy bulkcopy = new SqlBulkCopy(connection))
        //            {
        //                //Set destination table name
        //                //to table previously created.
        //                bulkcopy.DestinationTableName = "dbo.TestTable";

        //                try
        //                {
        //                    bulkcopy.WriteToServer(SourceTable);
        //                }
        //                catch (Exception ex)
        //                {
        //                    Console.WriteLine(ex.Message);
        //                }

        //                connection.Close();
        //            }
        //        }

        //        int intCnt = 0;
        //        return intCnt;


        //    }
        //    catch (Exception ex)
        //    {
        //        throw;
        //    }
        //    finally
        //    {
        //        this.CloseConnection();
        //    }
        //}

        //public DataType GetDataType(string dataType)
        //{
        //    DataType DTTemp = null;

        //    switch (dataType)
        //    {
        //        case ("System.Decimal"):
        //            DTTemp = DataType.Decimal(2, 18);
        //            break;
        //        case ("System.String"):
        //            DTTemp = DataType.VarChar(50);
        //            break;
        //        case ("System.Int32"):
        //            DTTemp = DataType.Int;
        //            break;
        //    }
        //    return DTTemp;
        //}

        public int CreateTableFromDtatable(DataTable SourceTable)
        {
            try
            {



                string connectionString = ConfigurationManager.ConnectionStrings[Constants.CONNECTION_STRING].ToString();
                SqlConnection Connection = new SqlConnection(connectionString);
                Connection.Open();
                SqlTransaction trans = Connection.BeginTransaction();

                SqlTableCreator sqlTCr = new SqlTableCreator(Connection, trans);
                sqlTCr.DestinationTableName = "temp_Xref";

                object obj = sqlTCr.CreateFromDataTable(SourceTable);
                trans.Commit();

                Connection.Close();


                //Open a connection with destination database;
                using (SqlConnection connection =
                       new SqlConnection(connectionString))
                {
                    connection.Open();

                    //Open bulkcopy connection.
                    using (SqlBulkCopy bulkcopy = new SqlBulkCopy(connection))
                    {
                        //Set destination table name
                        //to table previously created.
                        bulkcopy.DestinationTableName = "dbo.temp_Xref";

                        try
                        {
                            bulkcopy.WriteToServer(SourceTable);
                        }
                        catch (Exception ex)
                        {
                            Console.WriteLine(ex.Message);
                        }

                        connection.Close();
                    }
                }

                int intCnt = 0;
                return intCnt;


            }
            catch (Exception ex)
            {
                throw;
            }
            finally
            {
                this.CloseConnection();
            }
        }


        public int sqlBulkCopy(DataTable SourceTable)
        {
            try
            {

                string connectionString = ConfigurationManager.ConnectionStrings[Constants.CONNECTION_STRING].ToString();

                //Open a connection with destination database;
                using (SqlConnection connection =
                       new SqlConnection(connectionString))
                {
                    connection.Open();

                    //Open bulkcopy connection.
                    using (SqlBulkCopy bulkcopy = new SqlBulkCopy(connection))
                    {
                        //Set destination table name
                        //to table previously created.
                        bulkcopy.DestinationTableName = "dbo.temp_Xref";

                        try
                        {
                            bulkcopy.WriteToServer(SourceTable);
                        }
                        catch (Exception ex)
                        {
                            Console.WriteLine(ex.Message);
                        }

                        connection.Close();
                    }
                }

                int intCnt = 0;
                return intCnt;
            }
            catch (Exception ex)
            {
                throw;
            }
            finally
            {
                this.CloseConnection();
            }
        }

        public LoadOption Upsert { get; set; }

        public LoadOption PreserveChanges { get; set; }

        public LoadOption fAcceptChanges { get; set; }
    }
}
